import datetime

import pymysql


class DBTool:
    def __init__(self):
        self.db = pymysql.connect(host='34077v0j54.zicp.vip',
                                  port=44338,
                                  user='root',
                                  password='dzcxsys',
                                  db='USV')
        self.cursor = self.db.cursor()
        try:
            self.cursor.execute('USE USV;')
        except pymysql.err.Error as e:
            print(e)

    # 新建一个表格(LIKE pH)
    def create(self, table):
        sql = "CREATE TABLE %s LIKE pH;" % table
        try:
            self.cursor.execute(sql)
            print('Create Successfully')
        except pymysql.err.Error as e:
            print(e)

    # 插入表格数据到某一表中
    def insert(self, table, value):
        sql = "INSERT INTO {0}(TIME, VALUE) VALUES(%s, %s);".format(table)
        try:
            self.cursor.execute(sql, [datetime.datetime.today(), value])
            self.db.commit()
            print('Insert Successfully')
        except pymysql.err.Error as e:
            self.db.rollback()
            print(e)

    # 删除表格
    def drop(self, table):
        sql = "DROP TABLE %s;" % table
        try:
            self.cursor.execute(sql)
            print('Drop Successfully')
        except pymysql.err.Error as e:
            print(e)

    # 获取某段时间内的表格数据
    def select(self, table):
        sql = "SELECT * FROM {0} ORDER BY TIME DESC LIMIT 10".format(table)
        try:
            self.cursor.execute('FLUSH TABLES {0}'.format(table))
            self.cursor.execute(sql)
            data = self.cursor.fetchall()
            return data
        except pymysql.err.Error as e:
            print(e)

    def select_navigate(self):
        sql = "SELECT * FROM navigate"
        try:
            self.cursor.execute(sql)
            data = self.cursor.fetchall()
            return data
        except pymysql.err.Error as e:
            print(e)

    # 上传航行数据
    def update(self, table, key, value):
        sql = "UPDATE {0} SET {1} = %s WHERE name = 'USV';".format(table, key)
        try:
            self.cursor.execute(sql, value)
            self.db.commit()
            print('Update Successfully')
        except pymysql.err.Error as e:
            self.db.rollback()
            print(e)

    # 关闭数据库连接
    def close(self):
        try:
            self.cursor.close()
            self.db.close()
            print('Connection has already been closed')
        except pymysql.err.Error as e:
            print(e)
